﻿using System;
using System.Data;
using System.Text;
using System.Collections.Generic;
using System.Data.OracleClient;
namespace GSLMS.DAL.Oracle
{
	/// <summary>
	/// 数据访问类:APP_REFREGINFO
	/// </summary>
	public partial class APP_REFREGINFO : BaseDAL<GSLMS.Entity.TB_APP_REFREGINFO>
	{
		private readonly GSLMS.Core.Loger log = new Core.Loger();
		public APP_REFREGINFO()
		{}
		#region  Method
		/// <summary>
		///  增加一条数据
		/// </summary>
		public override bool Add(GSLMS.Entity.TB_APP_REFREGINFO model)
		{
			bool flag = false;
			OracleParameter[] parms =new OracleParameter[]{
								new OracleParameter("P_ID", model.ID),
								new OracleParameter("P_REGINFOID", model.REGINFOID),
								new OracleParameter("P_CUSTNAME", model.CUSTNAME),
								new OracleParameter("P_REFBY", model.REFBY),
								new OracleParameter("P_REFBYNAME", model.REFBYNAME),
								new OracleParameter("P_APPDATE", model.APPDATE),
								new OracleParameter("P_OUTDEPID", model.OUTDEPID),
								new OracleParameter("P_OUTDEPNAME", model.OUTDEPNAME),
								new OracleParameter("P_OUTTIME", model.OUTTIME),
								new OracleParameter("P_INDEPID", model.INDEPID),
								new OracleParameter("P_INDEPNAME", model.INDEPNAME),
								new OracleParameter("P_INTIME", model.INTIME),
								new OracleParameter("P_SALESMANID", model.SALESMANID),
								new OracleParameter("P_SALESMAN", model.SALESMAN),
								new OracleParameter("P_DETAIL", model.DETAIL),
								new OracleParameter("P_STATUS", model.STATUS),
								new OracleParameter("P_AUDITBY", model.AUDITBY),
								new OracleParameter("P_AUDITTIME", model.AUDITTIME),
								new OracleParameter("P_BACKSTATUS", model.BACKSTATUS),
								new OracleParameter("P_SPAREFIELD1", model.SPAREFIELD1),
								new OracleParameter("P_SPAREFIELD2", model.SPAREFIELD2),
								new OracleParameter("P_SPAREFIELD3", model.SPAREFIELD3)};
  
			for (int i = 0; i < 22; i++)
			{
				parms[i].Direction = ParameterDirection.Input;
			}

			try
			{
                OracleHelper.ExecuteNonQuery(OracleHelper.connectionString, CommandType.StoredProcedure, "pkg_apply.proc_refreginfo_add", parms);
				flag = true;
			}
			catch (Exception ex)
			{
				log.ExceptionHand(ex);
			}
			return flag;
		}

		/// <summary>
		///  更新一条数据
		/// </summary>
		public override bool Update(GSLMS.Entity.TB_APP_REFREGINFO model)
		{
			bool flag = false;
			OracleParameter[] parms =new OracleParameter[]{
								new OracleParameter("P_ID", model.ID),
								new OracleParameter("P_REGINFOID", model.REGINFOID),
								new OracleParameter("P_CUSTNAME", model.CUSTNAME),
								new OracleParameter("P_REFBY", model.REFBY),
								new OracleParameter("P_REFBYNAME", model.REFBYNAME),
								new OracleParameter("P_APPDATE", model.APPDATE),
								new OracleParameter("P_OUTDEPID", model.OUTDEPID),
								new OracleParameter("P_OUTDEPNAME", model.OUTDEPNAME),
								new OracleParameter("P_OUTTIME", model.OUTTIME),
								new OracleParameter("P_INDEPID", model.INDEPID),
								new OracleParameter("P_INDEPNAME", model.INDEPNAME),
								new OracleParameter("P_INTIME", model.INTIME),
								new OracleParameter("P_SALESMANID", model.SALESMANID),
								new OracleParameter("P_SALESMAN", model.SALESMAN),
								new OracleParameter("P_DETAIL", model.DETAIL),
								new OracleParameter("P_STATUS", model.STATUS),
								new OracleParameter("P_AUDITBY", model.AUDITBY),
								new OracleParameter("P_AUDITTIME", model.AUDITTIME),
								new OracleParameter("P_BACKSTATUS", model.BACKSTATUS),
								new OracleParameter("P_SPAREFIELD1", model.SPAREFIELD1),
								new OracleParameter("P_SPAREFIELD2", model.SPAREFIELD2),
								new OracleParameter("P_SPAREFIELD3", model.SPAREFIELD3)};
  
			for (int i = 0; i < 22; i++)
			{
				parms[i].Direction = ParameterDirection.Input;
			}

			try
			{
                OracleHelper.ExecuteNonQuery(OracleHelper.connectionString, CommandType.StoredProcedure, "pkg_apply.proc_refreginfo_update", parms);
				flag = true;
			}
			catch (Exception ex)
			{
				log.ExceptionHand(ex);
			}
			return flag;
		}

		/// <summary>
		///  删除一条数据
		/// </summary>
		public override bool Delete(long ID)
		{
			bool flag = false;
			OracleParameter[] parms =new OracleParameter[]{
								new OracleParameter("P_ID", ID)};
  
				parms[0].Direction = ParameterDirection.Input;

			try
			{
                OracleHelper.ExecuteNonQuery(OracleHelper.connectionString, CommandType.StoredProcedure, "pkg_apply.proc_refreginfo_delete", parms);
				flag = true;
			}
			catch (Exception ex)
			{
				log.ExceptionHand(ex);
			}
			return flag;
		}

		/// <summary>
		///DataRowToModel 
		/// </summary>
		private GSLMS.Entity.TB_APP_REFREGINFO DataRowToModel(DataRow dr)
		{
			if (dr != null && dr.ItemArray.Length > 0)
			{
				GSLMS.Entity.TB_APP_REFREGINFO model = new GSLMS.Entity.TB_APP_REFREGINFO();
				if(dr["ID"]!=null && dr["ID"].ToString()!="")
				{
					model.ID=OracleHelper.ToLong(dr["ID"].ToString());
				}
				if(dr["REGINFOID"]!=null && dr["REGINFOID"].ToString()!="")
				{
					model.REGINFOID=OracleHelper.ToLong(dr["REGINFOID"].ToString());
				}
				if(dr["CUSTNAME"]!=null && dr["CUSTNAME"].ToString()!="")
				{
					model.CUSTNAME=dr["CUSTNAME"].ToString();
				}

                if (dr["CUSTNO"]!=null&& dr["CUSTNO"].ToString() != "")
                {
                    model.CUSTNO = dr["CUSTNO"].ToString();
                }
                else
                    model.CUSTNO = "";

				if(dr["REFBY"]!=null && dr["REFBY"].ToString()!="")
				{
					model.REFBY=dr["REFBY"].ToString();
				}
				if(dr["REFBYNAME"]!=null && dr["REFBYNAME"].ToString()!="")
				{
					model.REFBYNAME=dr["REFBYNAME"].ToString();
				}
				if(dr["APPDATE"]!=null && dr["APPDATE"].ToString()!="")
				{
					model.APPDATE=DateTime.Parse(dr["APPDATE"].ToString());
				}
				if(dr["OUTDEPID"]!=null && dr["OUTDEPID"].ToString()!="")
				{
					model.OUTDEPID=OracleHelper.ToLong(dr["OUTDEPID"].ToString());
				}
				if(dr["OUTDEPNAME"]!=null && dr["OUTDEPNAME"].ToString()!="")
				{
					model.OUTDEPNAME=dr["OUTDEPNAME"].ToString();
				}
				if(dr["OUTTIME"]!=null && dr["OUTTIME"].ToString()!="")
				{
					model.OUTTIME=DateTime.Parse(dr["OUTTIME"].ToString());
				}
				if(dr["INDEPID"]!=null && dr["INDEPID"].ToString()!="")
				{
					model.INDEPID=OracleHelper.ToLong(dr["INDEPID"].ToString());
				}
				if(dr["INDEPNAME"]!=null && dr["INDEPNAME"].ToString()!="")
				{
					model.INDEPNAME=dr["INDEPNAME"].ToString();
				}
				if(dr["INTIME"]!=null && dr["INTIME"].ToString()!="")
				{
					model.INTIME=DateTime.Parse(dr["INTIME"].ToString());
				}
				if(dr["SALESMANID"]!=null && dr["SALESMANID"].ToString()!="")
				{
					model.SALESMANID=OracleHelper.ToLong(dr["SALESMANID"].ToString());
				}
				if(dr["SALESMAN"]!=null && dr["SALESMAN"].ToString()!="")
				{
					model.SALESMAN=dr["SALESMAN"].ToString();
				}
				if(dr["DETAIL"]!=null && dr["DETAIL"].ToString()!="")
				{
					model.DETAIL=dr["DETAIL"].ToString();
				}
				if(dr["STATUS"]!=null && dr["STATUS"].ToString()!="")
				{
                    model.STATUS = OracleHelper.ToInt(dr["STATUS"].ToString());
				}
				if(dr["AUDITBY"]!=null && dr["AUDITBY"].ToString()!="")
				{
					model.AUDITBY=dr["AUDITBY"].ToString();
				}
				if(dr["AUDITTIME"]!=null && dr["AUDITTIME"].ToString()!="")
				{
					model.AUDITTIME=DateTime.Parse(dr["AUDITTIME"].ToString());
				}
				if(dr["BACKSTATUS"]!=null && dr["BACKSTATUS"].ToString()!="")
				{
                    model.BACKSTATUS = OracleHelper.ToInt(dr["BACKSTATUS"].ToString());
				}
				if(dr["SPAREFIELD1"]!=null && dr["SPAREFIELD1"].ToString()!="")
				{
                    model.SPAREFIELD1 = OracleHelper.ToInt(dr["SPAREFIELD1"].ToString());
				}
				if(dr["SPAREFIELD2"]!=null && dr["SPAREFIELD2"].ToString()!="")
				{
					model.SPAREFIELD2=dr["SPAREFIELD2"].ToString();
				}
				if(dr["SPAREFIELD3"]!=null && dr["SPAREFIELD3"].ToString()!="")
				{
					model.SPAREFIELD3=dr["SPAREFIELD3"].ToString();
				}
				return model;
			}
			else
			{
				return null;
			}
		}

		/// <summary>
		/// 得到一个对象实体
		/// </summary>
		public override GSLMS.Entity.TB_APP_REFREGINFO GetByID(long ID)
		{
			GSLMS.Entity.TB_APP_REFREGINFO sModel = new GSLMS.Entity.TB_APP_REFREGINFO();
			string sql = "";
            sql = "select TB_APP_REFREGINFO.*,'' custno from TB_APP_REFREGINFO where id=" + ID.ToString();
			DataSet ds = OracleHelper.ExecuteDataset(sql);
			if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
			{
				sModel = DataRowToModel(ds.Tables[0].Rows[0]);
			}
			else
			{
			return null;
			}
			return sModel;
		}

        /// <summary>
        /// 分页提取数据
        /// </summary>
        public List<GSLMS.Entity.TB_APP_REFREGINFO> GetAuditList(GSLMS.Entity.TB_APP_REFREGINFO searchParam)
        {
            var list = new List<GSLMS.Entity.TB_APP_REFREGINFO>();
            DataTable dt = null;
            string tblName = "V_APP_REFREGINFO";
            string sqlWhere = "";
            string strSort = "";
            int pageSize = 20;
            int pageIndex = 1;
            string strFields = "*";
            int totalCount = 0;
            int countFlag = 1;

         
            //是否计算总数
            countFlag = (searchParam.Pagination.ReCount != null && searchParam.Pagination.ReCount) ? 1 : 0;
            pageSize = (searchParam.Pagination.PageSize != null && searchParam.Pagination.PageSize > 0) ? searchParam.Pagination.PageSize : 15;
            pageIndex = (searchParam.Pagination.CurrentIndex != null && searchParam.Pagination.CurrentIndex > 0) ? searchParam.Pagination.CurrentIndex : 1;
            strSort = searchParam.StrSort;

            //查询条件 客户名称 申请时间 状态

            if(searchParam.STATUS>=2)
                sqlWhere += " and STATUS="+searchParam.STATUS.ToString();
            else
            sqlWhere += " and STATUS>=2";

            if (searchParam.CUSTNAME != null && searchParam.CUSTNAME.Trim() != "")
            {
                sqlWhere = " and custname like '%" + OracleHelper.ReplaceUnsafeSql(searchParam.CUSTNAME) + "%'";
            }

            if (searchParam.CUSTNO != null && searchParam.CUSTNO.Trim() != "")
            {
                sqlWhere = " and CUSTNO like '%" + OracleHelper.ReplaceUnsafeSql(searchParam.CUSTNO) + "%'";
            }

            if (searchParam.AUDITBY != null && searchParam.AUDITBY.Trim() != "")
            {
                sqlWhere = " and AUDITBY='" + OracleHelper.ReplaceUnsafeSql(searchParam.AUDITBY) + "'";
            }

            if (searchParam.StarDateTime != null && searchParam.StarDateTime.ToString().Trim() != "")
            {
                sqlWhere += " and audittime>=to_date('" + Convert.ToDateTime(searchParam.StarDateTime).ToString("yyyy-MM-dd HH:mm:ss") + "','yyyy-mm-dd hh24:mi:ss')";
            }

            if (searchParam.EndDateTime != null && searchParam.EndDateTime.ToString().Trim() != "")
            {
                sqlWhere += " and audittime<to_date('" + Convert.ToDateTime(searchParam.EndDateTime).AddDays(1).AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss") + "','yyyy-mm-dd hh24:mi:ss')";
            }


            

            dt = OracleHelper.GetPageData(tblName, strFields, strSort, pageSize, pageIndex, countFlag, sqlWhere, out totalCount);
            if (searchParam.Pagination.ReCount)
            {
                searchParam.RowCount = totalCount;
                searchParam.Pagination.RowCount = totalCount;
            }
            if (dt != null && dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    GSLMS.Entity.TB_APP_REFREGINFO model = new GSLMS.Entity.TB_APP_REFREGINFO();

                    model = DataRowToModel(dt.Rows[i]);
                    list.Add(model);
                }
            }
            else
            {
                return null;
            }
            return list;
        }

		/// <summary>
		/// 分页提取数据
		/// </summary>
		public override List<GSLMS.Entity.TB_APP_REFREGINFO> GetList(GSLMS.Entity.TB_APP_REFREGINFO searchParam)
		{
			var list=new List<GSLMS.Entity.TB_APP_REFREGINFO>();
			DataTable dt = null;
			string tblName = "TB_APP_REFREGINFO";
			string sqlWhere="";
			string strSort="";
			int pageSize=20;
			int pageIndex=1;
            string strFields = "TB_APP_REFREGINFO.*,'' custno";
			int totalCount = 0;
			int countFlag = 1;

            //是否计算总数
            countFlag = (searchParam.Pagination.ReCount != null && searchParam.Pagination.ReCount) ? 1 : 0;
            pageSize = (searchParam.Pagination.PageSize != null && searchParam.Pagination.PageSize > 0) ? searchParam.Pagination.PageSize : 15;
            pageIndex = (searchParam.Pagination.CurrentIndex != null && searchParam.Pagination.CurrentIndex > 0) ? searchParam.Pagination.CurrentIndex : 1;
            strSort = searchParam.StrSort;


            //查询条件 客户名称 申请时间 状态

            if (searchParam.CUSTNAME != null && searchParam.CUSTNAME.Trim() != "")
            {
                sqlWhere += " and custname like '%" + OracleHelper.ReplaceUnsafeSql(searchParam.CUSTNAME) + "%'";
            }

            if (searchParam.StarDateTime != null && searchParam.StarDateTime.ToString().Trim() != "")
            {
                sqlWhere += " and AppDate>=to_date('" + Convert.ToDateTime(searchParam.StarDateTime).ToString("yyyy-MM-dd HH:mm:ss") + "','yyyy-mm-dd hh24:mi:ss')";
            }

            if (searchParam.EndDateTime != null && searchParam.EndDateTime.ToString().Trim() != "")
            {
                sqlWhere += " and AppDate<to_date('" + Convert.ToDateTime(searchParam.EndDateTime).AddDays(1).AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss") + "','yyyy-mm-dd hh24:mi:ss')";
            }


            if (searchParam.STATUS != null && searchParam.STATUS > -1)
            {
                sqlWhere += " and STATUS=" + searchParam.STATUS.ToString();
            }

			dt = OracleHelper.GetPageData(tblName, strFields, strSort, pageSize, pageIndex,countFlag, sqlWhere, out totalCount);

            if (searchParam.Pagination.ReCount)
            {
                searchParam.RowCount = totalCount;
                searchParam.Pagination.RowCount = totalCount;
            }
			if (dt != null && dt.Rows.Count > 0)
			{
				for (int i = 0; i < dt.Rows.Count; i++)
				{
					GSLMS.Entity.TB_APP_REFREGINFO model = new GSLMS.Entity.TB_APP_REFREGINFO();

					model = DataRowToModel(dt.Rows[i]);
					list.Add(model);
				}
			}
			else
			{
				return null;
			}
			return list;
		}

		#endregion  Method
	}
}

